import logging
import operator
import openpyxl
import datetime
import os
import sys
import configparser
import time
from hanchlogger import logger
import hanchxl
import ChengBanBuMen
import ReplaceBuMen

numDecimals = 2

timeStart = time.time()
timeCur = datetime.datetime.now()
# dirPath = 'E:\\日常工作\\11月\\1\\每日必办\\10月考核期'
# dirPath = 'E:\\日常工作\\数据组日常工作-春节\\生成每日上大屏文件'
dirPath = os.path.dirname(os.path.realpath(sys.executable))
dirPathShare = '\\\\10.117.115.111\\共享文件\\数据组\\区级考核裸分情况统计表'

logFile = dirPath + '\\AutoDaPing.log'
logger.get_log(logFile, 'LoggerAutoDaPing', logging.DEBUG)

iniFilePathCur = dirPath + '\\AutoDaPing.ini'
iniConfig = configparser.ConfigParser(allow_no_value=True)
iniConfig.read(iniFilePathCur, encoding='utf-8')

timeYesterdayStr = datetime.datetime.fromtimestamp(timeCur.timestamp()-60*60*24).strftime('%Y-%m-%d')
timeYesterdayStr = timeYesterdayStr + '  {}:{}:{}'.format(timeCur.hour, timeCur.minute, timeCur.second)
datatimeYesterday = datetime.datetime.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterday = time.strptime(timeYesterdayStr, '%Y-%m-%d %H:%M:%S')
timeYesterdayStamp = int(time.mktime(timeYesterday))
logger.debug('昨天（日期）：{}    昨天（时间戳）：{}'.format(timeYesterdayStr, timeYesterdayStamp))


def get_config_value(ini_config, section, section_key, value_default):
    sectionValue = value_default
    if ini_config.has_section(section):
        if ini_config.has_option(section, section_key):
            sectionValue = ini_config.get(section, section_key, )
            if sectionValue is not None and sectionValue != '':
                sectionValue = '{}'.format(sectionValue)
    return sectionValue


# 是否自定义工作目录
customizeWorkFolder = get_config_value(iniConfig, 'global', 'isCustomizeWorkFolder', '0')
if customizeWorkFolder != '0':
    workFolder = get_config_value(iniConfig, 'global', 'workFolder', dirPath)
    if workFolder != '' and dirPath != workFolder:
        if os.path.exists(workFolder):
            dirPath = workFolder
            logger.debug('是否自定义工作目录为：{}'.format(dirPath))
        else:
            logger.debug('自定义的工作目录不存在，工作目录：{}'.format(dirPath))
    else:
        logger.debug('工作目录未填写或者为空，或者与默认值相同，使用默认值：{}'.format(dirPath))
else:
    logger.debug('不使用自定义的工作目录，使用默认值:{}'.format(dirPath))

OrderCountFolder = get_config_value(iniConfig, 'global', 'OrderCountFolder', '')
if OrderCountFolder is not None and OrderCountFolder != '':
    dirPathShare = dirPath + OrderCountFolder
logger.debug('汇总文件目录：{}'.format(dirPathShare))

fileJieDan = dirPath + '\\本考期案件明细表-工单总数.xlsx'
fileQuKaoHe = dirPath + '\\本考期案件明细表-区级有效回访.xlsx'
fileShiKaoHe = dirPath + '\\本考期案件明细表-纳入市级考核范围.xlsx'
fileQuanBu = dirPath + '\\全部工单.xlsx'
fileJieDanPiPei = dirPath + '\\本考期案件明细表-工单总数-已匹配承办部门.xlsx'
fileQuKaoHePiPei = dirPath + '\\本考期案件明细表-区级有效回访-已匹配承办部门.xlsx'
fileShiKaoHePiPei = dirPath + '\\本考期案件明细表-纳入市级考核范围-已匹配承办部门.xlsx'
fileJieDanNo = dirPath + '\\本考期案件明细表-工单总数-未处理.xlsx'
fileShiKaoHeNo = dirPath + '\\本考期案件明细表-纳入市级考核范围-未处理.xlsx'
fileChengBanBuMen = dirPath + '\\承办部门.xlsx'
fileResultMuBan = dirPath + '\\模板-本考核期裸分情况统计表.xlsx'
fileResultKaoHe = dirPath + '\\本考期案件明细表-区级有效回访-处理结果.xlsx'
fileChengBanReplace = dirPath + '\\需替换部门.xlsx'
fileDaPing = dirPath + '\\模板-[内部] 拱辰街道接诉即办大屏排行榜.xlsx'
fileDaPingSave = dirPath + '\\[内部] 拱辰街道接诉即办大屏排行榜.xlsx'
fileLuoFenTongJi = dirPathShare + '\\{}.xlsx'.format(timeCur.year)
fileResultSave = ''

# '拱辰街道科室诉求总量反映情况统计（2022年10月17日0时至10月31日9时）'
titleDaPingKeShiJieDan = ''
titleDaPingSheQuJieDan = ''
titleDaPingKeShiShiFen = ''
titleDaPingSheQuShiFen = ''
strTitleSheQu = ''
strTitleKeShi = ''
strMain = ''
if timeCur.day <= 19:
    strTitleKeShi = '{}月考核期拱辰街道机关科室裸分情况统计表\r\n（'.format(timeCur.month)
    strTitleSheQu = '{}月考核期拱辰街道社区（村）裸分情况统计表\r\n（'.format(timeCur.month)
    titleDaPingKeShiJieDan = '{}月考核期拱辰街道机关科室接单量排行榜'.format(timeCur.month)
    titleDaPingSheQuJieDan = '{}月考核期拱辰街道社区（村）接单量排行榜'.format(timeCur.month)
    titleDaPingKeShiShiFen = '{}月考核期拱辰街道机关科室裸分失分排行'.format(timeCur.month)
    titleDaPingSheQuShiFen = '{}月考核期拱辰街道社区（村）裸分失分排行'.format(timeCur.month)
    fileResultSave = dirPath + '\\{}月考核期裸分情况统计表.xlsx'.format(timeCur.month)
    strMain = '{}月考核期（截至{}月{}日9时），'.format(timeCur.month, timeCur.month, timeCur.day)
    if timeCur.month == 1:
        strTitleKeShi = strTitleKeShi + '{}年12月19日0时至'.format(timeCur.year - 1)
        strTitleKeShi = strTitleKeShi + '{}年{}月{}日9时)'.format(timeCur.year, timeCur.month, timeCur.day)
        strTitleSheQu = strTitleSheQu + '{}年12月19日0时至'.format(timeCur.year - 1)
        strTitleSheQu = strTitleSheQu + '{}年{}月{}日9时)'.format(timeCur.year, timeCur.month, timeCur.day)
    else:
        strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month - 1)
        strTitleKeShi = strTitleKeShi + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
        strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month - 1)
        strTitleSheQu = strTitleSheQu + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
else:
    if timeCur.month == 12:
        strTitleKeShi = '1月考核期拱辰街道机关科室裸分情况统计表\r\n（'
        strTitleSheQu = '1月考核期拱辰街道社区（村）裸分情况统计表\r\n（'
        titleDaPingKeShiJieDan = '1月考核期拱辰街道机关科室接单量排行榜'
        titleDaPingSheQuJieDan = '1月考核期拱辰街道社区（村）接单量排行榜'
        titleDaPingKeShiShiFen = '1月考核期拱辰街道机关科室裸分失分排行'
        titleDaPingSheQuShiFen = '1月考核期拱辰街道社区（村）裸分失分排行'
        fileResultSave = dirPath + '\\1月考核期裸分情况统计表.xlsx'
        strMain = '1月考核期（截至{}年{}月{}日9时），'.format(timeCur.year, timeCur.month, timeCur.day)
    else:
        strTitleKeShi = '{}月考核期拱辰街道机关科室裸分情况统计表\r\n（'.format(timeCur.month + 1)
        strTitleSheQu = '{}月考核期拱辰街道社区（村）裸分情况统计表\r\n（'.format(timeCur.month + 1)
        titleDaPingKeShiJieDan = '{}月考核期拱辰街道机关科室接单量排行榜'.format(timeCur.month + 1)
        titleDaPingSheQuJieDan = '{}月考核期拱辰街道社区（村）接单量排行榜'.format(timeCur.month + 1)
        titleDaPingKeShiShiFen = '{}月考核期拱辰街道机关科室裸分失分排行'.format(timeCur.month + 1)
        titleDaPingSheQuShiFen = '{}月考核期拱辰街道社区（村）裸分失分排行'.format(timeCur.month + 1)
        fileResultSave = dirPath + '\\{}月考核期裸分情况统计表.xlsx'.format(timeCur.month + 1)
        strMain = '{}月考核期（截至{}月{}日9时），'.format(timeCur.month + 1, timeCur.month, timeCur.day)
    strTitleKeShi = strTitleKeShi + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
    strTitleKeShi = strTitleKeShi + '{}月{}日9时)'.format(timeCur.month, timeCur.day)
    strTitleSheQu = strTitleSheQu + '{}年{}月19日0时至'.format(timeCur.year, timeCur.month)
    strTitleSheQu = strTitleSheQu + '{}月{}日9时)'.format(timeCur.month, timeCur.day)

bookQuKaoHe = openpyxl.load_workbook(fileQuKaoHe)
sheetQuKaoHe = bookQuKaoHe.active
maxRowZongLiang = sheetQuKaoHe.max_row
maxColZongLiang = sheetQuKaoHe.max_column
logger.debug('本考期案件明细表-区级有效回访.xlsx -------行数：{}行    列数：{}列'.format(maxRowZongLiang, maxColZongLiang))

bookQuanBu = openpyxl.load_workbook(fileQuanBu)
sheetQuanBu = bookQuanBu.active
maxRowQuanBu = sheetQuanBu.max_row
maxColQuanBu = sheetQuanBu.max_column
logger.debug('全部工单.xlsx -------行数：{}行    列数：{}列'.format(maxRowQuanBu, maxColQuanBu))

colCanYuQuIndex = hanchxl.get_col_index(sheetQuKaoHe, '参与部门')
sheetQuKaoHe.cell(1, colCanYuQuIndex).value = '参与部门（区）'

logger.debug('考核工单-插入承办部门 是否处理 用时{}秒'.format(round(time.time() - timeStart, 2)))
colChengBan = hanchxl.get_col_index(sheetQuKaoHe, '承办单位')
sheetQuKaoHe.insert_cols(colChengBan, 3)
sheetQuKaoHe.cell(1, colChengBan).value = '承办部门'
sheetQuKaoHe.cell(1, colChengBan + 1).value = '参与部门'
sheetQuKaoHe.cell(1, colChengBan + 2).value = '是否处理'

logger.debug('考核工单-匹配承办部门 用时{}秒'.format(round(time.time() - timeStart, 2)))

# 打开替换部门字典表
logger.debug('打开替换部门字典表 文件：{}'.format(fileChengBanReplace))
replaceBuMen = ReplaceBuMen.ReplaceDirectory()
replaceBuMen.load_directory(fileChengBanReplace)
listReplaceBuMen = replaceBuMen.get_replace_directory()

# # 匹配区系统 承办部门 和 参与部门
# hanchxl.pi_pei_cheng_ban_qu_xi_tong(sheetKaoHe, '参与部门（区）', '承办部门', '参与部门')
hanchxl.pi_pei_cheng_ban_qu_xi_tong_2(sheetQuKaoHe, '主办部门名称', '从办部门名称', '承办部门', '参与部门')
# # 根据 ‘工单编号’ 匹配 ‘承办部门’ 和 ‘参与部门’
hanchxl.pi_pei_te_ding_col(sheetQuKaoHe, '工单编号', sheetQuanBu, '承办部门', '参与部门')

for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetQuKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetQuKaoHe, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetQuKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetQuKaoHe, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)

hanchxl.remove_repeat_value(sheetQuKaoHe, '承办部门', '参与部门')
# 保存匹配承办部门后的表格
logger.debug('保存匹配承办部门后的表格 用时{}秒'.format(round(time.time() - timeStart, 2)))
# bookQuKaoHe.save(fileQuKaoHePiPei)
# logger.debug('保存  {}'.format(fileQuKaoHePiPei))

# 打开 本考期案件明细表-总量.xlsx
logger.debug('打开 本考期案件明细表-总量.xlsx 用时{}秒'.format(round(time.time() - timeStart, 2)))
bookJieDan = openpyxl.load_workbook(fileJieDan)
sheetJieDan = bookJieDan.active
maxRowJieDan = sheetJieDan.max_row
maxColJieDan = sheetJieDan.max_column
logger.debug('本考期案件明细表-总量.xlsx -------行数：{}行    列数：{}列'.format(maxRowJieDan, maxColJieDan))

colCanYuQuIndex = hanchxl.get_col_index(sheetJieDan, '参与部门')
sheetJieDan.cell(1, colCanYuQuIndex).value = '参与部门（区）'

logger.debug('接单总量-插入承办部门 是否处理 用时{}秒'.format(round(time.time() - timeStart, 2)))
colChengBanJieDan = hanchxl.get_col_index(sheetJieDan, '承办单位')
sheetJieDan.insert_cols(colChengBanJieDan, 3)
sheetJieDan.cell(1, colChengBanJieDan).value = '承办部门'
sheetJieDan.cell(1, colChengBanJieDan+1).value = '参与部门'
sheetJieDan.cell(1, colChengBanJieDan+2).value = '是否处理'

logger.debug('接单总量-匹配承办部门 用时{}秒'.format(round(time.time() - timeStart, 2)))

# 匹配区系统 承办部门 和 参与部门
# hanchxl.pi_pei_cheng_ban_qu_xi_tong(sheetJieDan, '参与部门（区）', '承办部门', '参与部门')
hanchxl.pi_pei_cheng_ban_qu_xi_tong_2(sheetJieDan, '主办部门名称', '从办部门名称', '承办部门', '参与部门')
hanchxl.pi_pei_te_ding_col(sheetJieDan, '工单编号', sheetQuanBu, '承办部门', '参与部门')

for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDan, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetJieDan, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetJieDan, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)

hanchxl.remove_repeat_value(sheetJieDan, '承办部门', '参与部门')
# 保存匹配承办部门后的表格
logger.debug('接单总量-保存匹配承办部门后的表格 用时{}秒'.format(round(time.time() - timeStart, 2)))
# bookJieDan.save(fileJieDanPiPei)
# logger.debug('保存  {}'.format(fileJieDanPiPei))

# 打开 本考期案件明细表-纳入市级考核范围.xlsx
logger.debug('打开 本考期案件明细表-纳入市级考核范围.xlsx 用时{}秒'.format(round(time.time() - timeStart, 2)))
bookShiKaoHe = openpyxl.load_workbook(fileShiKaoHe)
sheetShiKaoHe = bookShiKaoHe.active
maxRowShiKaoHe = sheetShiKaoHe.max_row
maxColShiKaoHe = sheetShiKaoHe.max_column
logger.debug('本考期案件明细表-纳入市级考核范围.xlsx -------行数：{}行    列数：{}列'.format(maxRowShiKaoHe, maxColShiKaoHe))

colCanYuQuIndex = hanchxl.get_col_index(sheetShiKaoHe, '参与部门')
sheetShiKaoHe.cell(1, colCanYuQuIndex).value = '参与部门（区）'

logger.debug('本考期案件明细表-纳入市级考核范围-插入承办部门 是否处理 用时{}秒'.format(round(time.time() - timeStart, 2)))
colChengBanShiKaoHe = hanchxl.get_col_index(sheetShiKaoHe, '承办单位')
sheetShiKaoHe.insert_cols(colChengBanShiKaoHe, 3)
sheetShiKaoHe.cell(1, colChengBanShiKaoHe).value = '承办部门'
sheetShiKaoHe.cell(1, colChengBanShiKaoHe+1).value = '参与部门'
sheetShiKaoHe.cell(1, colChengBanShiKaoHe+2).value = '是否处理'

logger.debug('本考期案件明细表-纳入市级考核范围-匹配承办部门 用时{}秒'.format(round(time.time() - timeStart, 2)))

# 匹配区系统 承办部门 和 参与部门
hanchxl.pi_pei_cheng_ban_qu_xi_tong_2(sheetShiKaoHe, '主办部门名称', '从办部门名称', '承办部门', '参与部门')
hanchxl.pi_pei_te_ding_col(sheetShiKaoHe, '工单编号', sheetQuanBu, '承办部门', '参与部门')

for buMenInfo in listReplaceBuMen:
    if buMenInfo.isAll is True:
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetShiKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men_all(sheetShiKaoHe, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)
    else:
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetShiKaoHe, '承办部门', buMenInfo.nameYuan, buMenInfo.nameNow)
        hanchxl.xiu_gai_cheng_ban_bu_men(sheetShiKaoHe, '参与部门', buMenInfo.nameYuan, buMenInfo.nameNow)

hanchxl.remove_repeat_value(sheetShiKaoHe, '承办部门', '参与部门')
# 保存匹配承办部门后的表格
logger.debug('本考期案件明细表-纳入市级考核范围-保存匹配承办部门后的表格 用时{}秒'.format(round(time.time() - timeStart, 2)))
bookShiKaoHe.save(fileShiKaoHePiPei)
logger.debug('保存  {}'.format(fileShiKaoHePiPei))

colJieDanCount = 3              # 接单总量
colJieDanCountZhuan = 4              # 转派-接单总量
colJieDanCountZhi = 5              # 直派-接单总量
colShiKaoHeCount = 6              # 市级考核总量
colShiKaoHeCountZhuan = 7              # 转派-市级考核总量
colShiKaoHeCountZhi = 8              # 直派-市级考核总量
colQuKaoHeCount = 9                  # 区级有效回访总量

colZhuanKaoHe = 10              # 转派-考核
colZhuanLian = 11              # 转派-联系
colZhuanLianNo = 12            # 转派-未联系
colZhuanJie = 13              # 转派-解决
colZhuanJieNo = 14            # 转派-未解决
colZhuanMan = 15              # 转派-满意
colZhuanJiBen = 16            # 转派-基本满意
colZhuanManNo = 17             # 转派-不满意
colZhuanJian = 18                #转派-见面
colZhuanJianNo = 19              #转派-未见面
colZhuanlvLian = 20              # 转派-响应率
colZhuanLvJie = 21             # 转派-解决率
colZhuanLvMan = 22            # 转派-满意率
colZhuanLvJian = 23              # 转派-见面率

colZhiKaoHe = 24              # 直派-考核
colZhiLian = 25              # 直派-联系
colZhiLianNo = 26            # 直派-未联系
colZhiJie = 27              # 直派-解决
colZhiJieNo = 28            # 直派-未解决
colZhiMan = 29              # 直派-满意
colZhiJiBen = 30            # 直派-基本满意
colZhiManNo = 31             # 直派-不满意
colZhiJian = 32              # 直派-见面
colZhiJianNo = 33              # 直派-未见面
colZhilvLian = 34              # 直派-响应率
colZhiLvJie = 35             # 直派-解决率
colZhiLvMan = 36            # 直派-满意率
colZhilvJian = 37              # 直派-见面率

colAvgLian = 38             # 平均响应率
colAvgJie = 39              # 平均解决率
colAvgMan = 40              # 平均满意率
colAvgJian = 41              # 平均见面率

colDeFen = 42                   # 得分
colPaiming = 43                 # 排名
colDeFenLuoFen = 44             # 裸分得分
colShiFenLuoFen = 45            # 裸分失分
colSuoShuPianQu = 46            # 所属片区

bookResultMuBan = openpyxl.load_workbook(fileResultMuBan)
sheetKeShiResult = bookResultMuBan['科室']
sheetSheQuResult = bookResultMuBan['社区、村']

# 打开承办部门字典表
logger.debug('打开承办部门字典表 文件：{}'.format(fileChengBanBuMen))
chengBan = ChengBanBuMen.ChengBanDirectory()
chengBan.load_cheng_ban_list(fileChengBanBuMen)
listChengBanKeShi = chengBan.get_list_ke_shi()
listChengBanSheQu = chengBan.get_list_she_qu()

listKeShi = []
listSheQu = []
listKaoHeZhuan = {}
listKaoHeZhi = {}
listLianXiZhuan = {}
listLianXiZhi = {}
listWeiLianXiZhuan = {}
listWeiLianXiZhi = {}
listJieJueZhuan = {}
listJieJueZhi = {}
listWeiJieJueZhuan = {}
listWeiJieJueZhi = {}
listManYiZhuan = {}
listManYiZhi = {}
listJiBenZhuan = {}
listJiBenZhi = {}
listBuManYiZhuan = {}
listBuManYiZhi = {}
listJianMianZhuan = {}
listJianMianZhi = {}
listWeiJianMianZhuan = {}
listWeiJianMianZhi = {}

lvLianXiZhi = {}
lvWeiLianXiZhi = {}
lvJieJueZhi = {}
lvWeiJieJueZhi = {}
lvManYiZhi = {}
lvJiBenZhi = {}
lvBuManYiZhi = {}
lvManYiFinalZhi = {}
lvJianMianZhi = {}
lvWeiJianMianZhi = {}
lvLianXiZhuan = {}
lvWeiLianXiZhuan = {}
lvJieJueZhuan = {}
lvWeiJieJueZhuan = {}
lvManYiZhuan = {}
lvJiBenZhuan = {}
lvBuManYiZhuan = {}
lvManYiFinalZhuan = {}
lvJianMianZhuan = {}
lvWeiJianMianZhuan = {}


lvLianXiAverage = {}
lvWeiLianXiAverage = {}
lvJieJueAverage = {}
lvWeiJieJueAverage = {}
lvManYiAverage = {}
lvJiBenAverage = {}
lvBuManYiAverage = {}
lvManYiFinalAverage = {}
lvJianMianAverage = {}
lvWeiJianMianAverage = {}

listJieDanZhi = {}
listJieDanZhuan = {}

listShiKaoHeZhi = {}
listShiKaoHeZhuan = {}

deFenLuoFen = {}
shiFenLuoFen = {}

# 社区村的所属片区
suoShuPianQu = {}
# 社区村（科室）的负责人
fuZeRen = {}
# 社区村（科室）的主管领导
zhuGuanLingDao = {}

# 保存科室接单量排名
listJieDanKeShi = {}
# 保存社区村接单量排名
listJieDanSheQu = {}
# 保存科室裸分失分排名
listShiFenKeshi = {}
# 保存社区村裸分失分排名
listShiFenSheQu = {}

listShiFenFenLei = {}


logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))

for valueCellKeShi in listChengBanKeShi:
    listKeShi.append(valueCellKeShi)
    listKaoHeZhuan[valueCellKeShi] = 0
    listKaoHeZhi[valueCellKeShi] = 0
    listLianXiZhuan[valueCellKeShi] = 0
    listLianXiZhi[valueCellKeShi] = 0
    listJieJueZhuan[valueCellKeShi] = 0
    listJieJueZhi[valueCellKeShi] = 0
    listManYiZhuan[valueCellKeShi] = 0
    listManYiZhi[valueCellKeShi] = 0
    listJiBenZhuan[valueCellKeShi] = 0
    listJiBenZhi[valueCellKeShi] = 0
    listWeiLianXiZhuan[valueCellKeShi] = 0
    listWeiLianXiZhi[valueCellKeShi] = 0
    listWeiJieJueZhuan[valueCellKeShi] = 0
    listWeiJieJueZhi[valueCellKeShi] = 0
    listBuManYiZhuan[valueCellKeShi] = 0
    listBuManYiZhi[valueCellKeShi] = 0
    listJieDanZhi[valueCellKeShi] = 0
    listJieDanZhuan[valueCellKeShi] = 0
    listShiKaoHeZhi[valueCellKeShi] = 0
    listShiKaoHeZhuan[valueCellKeShi] = 0
    listJianMianZhuan[valueCellKeShi] = 0
    listJianMianZhi[valueCellKeShi] = 0
    listWeiJianMianZhuan[valueCellKeShi] = 0
    listWeiJianMianZhi[valueCellKeShi] = 0
    lvLianXiZhi[valueCellKeShi] = None
    lvWeiLianXiZhi[valueCellKeShi] = None
    lvJieJueZhi[valueCellKeShi] = None
    lvWeiJieJueZhi[valueCellKeShi] = None
    lvManYiZhi[valueCellKeShi] = None
    lvJiBenZhi[valueCellKeShi] = None
    lvBuManYiZhi[valueCellKeShi] = None
    lvManYiFinalZhi[valueCellKeShi] = None
    lvManYiFinalZhi[valueCellKeShi] = None
    lvLianXiZhuan[valueCellKeShi] = None
    lvWeiLianXiZhuan[valueCellKeShi] = None
    lvJieJueZhuan[valueCellKeShi] = None
    lvWeiJieJueZhuan[valueCellKeShi] = None
    lvManYiZhuan[valueCellKeShi] = None
    lvJiBenZhuan[valueCellKeShi] = None
    lvBuManYiZhuan[valueCellKeShi] = None
    lvManYiFinalZhuan[valueCellKeShi] = None
    lvLianXiAverage[valueCellKeShi] = None
    lvWeiLianXiAverage[valueCellKeShi] = None
    lvJieJueAverage[valueCellKeShi] = None
    lvWeiJieJueAverage[valueCellKeShi] = None
    lvManYiAverage[valueCellKeShi] = None
    lvJiBenAverage[valueCellKeShi] = None
    lvBuManYiAverage[valueCellKeShi] = None
    lvManYiFinalAverage[valueCellKeShi] = None
    lvJianMianZhuan[valueCellKeShi] =None
    lvWeiJianMianZhuan[valueCellKeShi] = None
    lvJianMianZhi[valueCellKeShi] = None
    lvWeiJianMianZhi[valueCellKeShi] = None
    lvJianMianAverage[valueCellKeShi] = None
    lvWeiJianMianAverage[valueCellKeShi] = None
    deFenLuoFen[valueCellKeShi] = None
    shiFenLuoFen[valueCellKeShi] = None
    fuZeRen[valueCellKeShi] = listChengBanKeShi[valueCellKeShi].fuZeRen
    zhuGuanLingDao[valueCellKeShi] = listChengBanKeShi[valueCellKeShi].zhuGuanLingDao
    listShiFenFenLei[valueCellKeShi] = {}


for valueCellSheQu in listChengBanSheQu:
    listSheQu.append(valueCellSheQu)
    listKaoHeZhuan[valueCellSheQu] = 0
    listKaoHeZhi[valueCellSheQu] = 0
    listLianXiZhuan[valueCellSheQu] = 0
    listLianXiZhi[valueCellSheQu] = 0
    listJieJueZhuan[valueCellSheQu] = 0
    listJieJueZhi[valueCellSheQu] = 0
    listManYiZhuan[valueCellSheQu] = 0
    listManYiZhi[valueCellSheQu] = 0
    listJiBenZhuan[valueCellSheQu] = 0
    listJiBenZhi[valueCellSheQu] = 0
    listWeiLianXiZhuan[valueCellSheQu] = 0
    listWeiLianXiZhi[valueCellSheQu] = 0
    listWeiJieJueZhuan[valueCellSheQu] = 0
    listWeiJieJueZhi[valueCellSheQu] = 0
    listBuManYiZhuan[valueCellSheQu] = 0
    listBuManYiZhi[valueCellSheQu] = 0
    listJieDanZhi[valueCellSheQu] = 0
    listJieDanZhuan[valueCellSheQu] = 0
    listShiKaoHeZhi[valueCellSheQu] = 0
    listShiKaoHeZhuan[valueCellSheQu] = 0
    listJianMianZhuan[valueCellSheQu] = 0
    listJianMianZhi[valueCellSheQu] = 0
    listWeiJianMianZhuan[valueCellSheQu] = 0
    listWeiJianMianZhi[valueCellSheQu] = 0
    lvLianXiZhi[valueCellSheQu] = None
    lvWeiLianXiZhi[valueCellSheQu] = None
    lvJieJueZhi[valueCellSheQu] = None
    lvWeiJieJueZhi[valueCellSheQu] = None
    lvManYiZhi[valueCellSheQu] = None
    lvJiBenZhi[valueCellSheQu] = None
    lvBuManYiZhi[valueCellSheQu] = None
    lvManYiFinalZhi[valueCellSheQu] = None
    lvLianXiZhuan[valueCellSheQu] = None
    lvWeiLianXiZhuan[valueCellSheQu] = None
    lvJieJueZhuan[valueCellSheQu] = None
    lvWeiJieJueZhuan[valueCellSheQu] = None
    lvManYiZhuan[valueCellSheQu] = None
    lvJiBenZhuan[valueCellSheQu] = None
    lvBuManYiZhuan[valueCellSheQu] = None
    lvManYiFinalZhuan[valueCellSheQu] = None
    lvLianXiAverage[valueCellSheQu] = None
    lvWeiLianXiAverage[valueCellSheQu] = None
    lvJieJueAverage[valueCellSheQu] = None
    lvWeiJieJueAverage[valueCellSheQu] = None
    lvManYiAverage[valueCellSheQu] = None
    lvJiBenAverage[valueCellSheQu] = None
    lvBuManYiAverage[valueCellSheQu] = None
    lvManYiFinalAverage[valueCellSheQu] = None
    lvJianMianZhuan[valueCellSheQu] =None
    lvWeiJianMianZhuan[valueCellSheQu] = None
    lvJianMianZhi[valueCellSheQu] = None
    lvWeiJianMianZhi[valueCellSheQu] = None
    lvJianMianAverage[valueCellSheQu] = None
    lvWeiJianMianAverage[valueCellSheQu] = None
    deFenLuoFen[valueCellSheQu] = None
    shiFenLuoFen[valueCellSheQu] = None
    deFenLuoFen[valueCellSheQu] = None
    shiFenLuoFen[valueCellSheQu] = None
    suoShuPianQu[valueCellSheQu] = listChengBanSheQu[valueCellSheQu].suoShuPianQu
    fuZeRen[valueCellSheQu] = listChengBanSheQu[valueCellSheQu].fuZeRen
    zhuGuanLingDao[valueCellSheQu] = listChengBanSheQu[valueCellSheQu].zhuGuanLingDao
    listShiFenFenLei[valueCellSheQu] = {}


logger.debug('承办部门-科室：{}'.format(listKeShi))
logger.debug('承办部门-社区/村：{}'.format(listSheQu))

colChengBanJieDan = hanchxl.get_col_index(sheetJieDan, '承办部门')
colLeiXingJieDan = hanchxl.get_col_index(sheetJieDan, '派单类型')
colJieDanChuLi = hanchxl.get_col_index(sheetJieDan, '是否处理')

maxRowJieDan = sheetJieDan.max_row
logger.debug('maxRowJieDan ：{}'.format(maxRowJieDan))
logger.debug('开始统计接单数据 用时{}秒'.format(round(time.time() - timeStart, 2)))
numWeiChuLi = 0
for lineIndexJieDan in range(2, maxRowJieDan+1):
    valueChengBanJieDan = sheetJieDan.cell(lineIndexJieDan, colChengBanJieDan).value
    valueLeiXingJieDan = sheetJieDan.cell(lineIndexJieDan, colLeiXingJieDan).value
    if valueChengBanJieDan is not None and valueLeiXingJieDan is not None:
        valueChengBanJieDan = valueChengBanJieDan.strip()
        valueLeiXingJieDan = valueLeiXingJieDan.strip()
        logger.debug('{} - {}'.format(valueChengBanJieDan, valueLeiXingJieDan))
        if valueChengBanJieDan in listKeShi or valueChengBanJieDan in listSheQu:
            if valueLeiXingJieDan == '直派' or valueLeiXingJieDan == '市派':
                listJieDanZhi[valueChengBanJieDan] = listJieDanZhi[valueChengBanJieDan] + 1
                sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '已处理-直派'
            else:
                if valueLeiXingJieDan == '转派' or valueLeiXingJieDan == '区派':
                    listJieDanZhuan[valueChengBanJieDan] = listJieDanZhuan[valueChengBanJieDan] + 1
                    sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '已处理-转派'
                else:
                    numWeiChuLi = numWeiChuLi + 1
                    sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '未处理'
        else:
            numWeiChuLi = numWeiChuLi + 1
            sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '未处理-无承办部门'
    else:
        numWeiChuLi = numWeiChuLi + 1
        sheetJieDan.cell(lineIndexJieDan, colJieDanChuLi).value = '未处理-数据错误'

logger.debug('接单量-直派：{}'.format(listJieDanZhi))
logger.debug('接单量-转派：{}'.format(listJieDanZhuan))
logger.debug('统计接单数据完成 用时{}秒'.format(round(time.time() - timeStart, 2)))
# maxRowJieDan2 = sheetJieDan.max_row
if numWeiChuLi > 0:
    # 保存未匹配承办部门的表格
    bookJieDan.save(fileJieDanNo)
    logger.debug('保存  {}'.format(fileJieDanNo))

colChengBanShiKaoHe = hanchxl.get_col_index(sheetShiKaoHe, '承办部门')
colLeiXingShiKaoHe = hanchxl.get_col_index(sheetShiKaoHe, '派单类型')
colShiKaoHeChuLi = hanchxl.get_col_index(sheetShiKaoHe, '是否处理')

maxRowShiKaoHe = sheetShiKaoHe.max_row
logger.debug('maxRowJieDan ：{}'.format(maxRowShiKaoHe))
logger.debug('开始统计 纳入市级考核范围 数据 用时{}秒'.format(round(time.time() - timeStart, 2)))
numWeiChuLi = 0
for lineIndexShiKaoHe in range(2, maxRowShiKaoHe+1):
    valueChengBanShiKaoHe = sheetShiKaoHe.cell(lineIndexShiKaoHe, colChengBanShiKaoHe).value
    valueLeiXingShiKaoHe = sheetShiKaoHe.cell(lineIndexShiKaoHe, colLeiXingShiKaoHe).value
    if valueChengBanShiKaoHe is not None and valueLeiXingShiKaoHe is not None:
        valueChengBanJieDan = valueChengBanShiKaoHe.strip()
        valueLeiXingShiKaoHe = valueLeiXingShiKaoHe.strip()
        logger.debug('{} - {}'.format(valueChengBanShiKaoHe, valueLeiXingShiKaoHe))
        if valueChengBanShiKaoHe in listKeShi or valueChengBanShiKaoHe in listSheQu:
            if valueLeiXingShiKaoHe == '直派' or valueLeiXingShiKaoHe == '市派':
                listShiKaoHeZhi[valueChengBanShiKaoHe] = listShiKaoHeZhi[valueChengBanShiKaoHe] + 1
                sheetShiKaoHe.cell(lineIndexShiKaoHe, colShiKaoHeChuLi).value = '已处理-直派'
            else:
                if valueLeiXingShiKaoHe == '转派' or valueLeiXingShiKaoHe == '区派':
                    listShiKaoHeZhuan[valueChengBanShiKaoHe] = listShiKaoHeZhuan[valueChengBanShiKaoHe] + 1
                    sheetShiKaoHe.cell(lineIndexShiKaoHe, colShiKaoHeChuLi).value = '已处理-转派'
                else:
                    numWeiChuLi = numWeiChuLi + 1
                    sheetShiKaoHe.cell(lineIndexShiKaoHe, colShiKaoHeChuLi).value = '未处理'
        else:
            numWeiChuLi = numWeiChuLi + 1
            sheetShiKaoHe.cell(lineIndexShiKaoHe, colShiKaoHeChuLi).value = '未处理-无承办部门'
    else:
        numWeiChuLi = numWeiChuLi + 1
        sheetJieDan.cell(lineIndexJieDan, colShiKaoHeChuLi).value = '未处理-数据错误'

logger.debug('接单量-直派：{}'.format(listShiKaoHeZhi))
logger.debug('接单量-转派：{}'.format(listShiKaoHeZhuan))
logger.debug('统计 纳入市级考核范围 数据完成 用时{}秒'.format(round(time.time() - timeStart, 2)))
# maxRowJieDan2 = sheetJieDan.max_row
if numWeiChuLi > 0:
    # 保存未匹配承办部门的表格
    bookShiKaoHe.save(fileShiKaoHeNo)
    logger.debug('保存  {}'.format(fileShiKaoHeNo))

colChengBanIndex = hanchxl.get_col_index(sheetQuKaoHe, '承办部门')
colLeiXing = hanchxl.get_col_index(sheetQuKaoHe, '派单类型')
colLianXi = hanchxl.get_col_index(sheetQuKaoHe, '是否联系')
colJieJue = hanchxl.get_col_index(sheetQuKaoHe, '是否解决')
colManYi = hanchxl.get_col_index(sheetQuKaoHe, '是否满意')
colJianMian = hanchxl.get_col_index(sheetQuKaoHe, '是否见面')
colChuLi = hanchxl.get_col_index(sheetQuKaoHe, '是否处理')
colErJiFenLei = hanchxl.get_col_index(sheetQuKaoHe, '二级分类')
logger.debug('承办部门：{}列  '.format(colChengBanIndex))
logger.debug('派单类型：{}列'.format(colLeiXing))
logger.debug('是否联系：{}列'.format(colLianXi))
logger.debug('是否解决：{}列'.format(colJieJue))
logger.debug('是否满意：{}列'.format(colManYi))
logger.debug('是否处理：{}列'.format(colChuLi))
logger.debug('是否见面：{}列'.format(colJianMian))
logger.debug('开始统计考核数据 用时{}秒'.format(round(time.time() - timeStart, 2)))
lineIndex = 2
numWeiChuLiKaoHe = 0
# while lineIndex <= maxRowQuanBu:
for lineIndex in range(2, maxRowZongLiang+1):
    valueChengBanCur = sheetQuKaoHe.cell(lineIndex, colChengBanIndex).value
    valueLeiXing = sheetQuKaoHe.cell(lineIndex, colLeiXing).value
    valueLianxi = sheetQuKaoHe.cell(lineIndex, colLianXi).value
    valueJieJue = sheetQuKaoHe.cell(lineIndex, colJieJue).value
    valueManYi = sheetQuKaoHe.cell(lineIndex, colManYi).value
    valueJianMian = sheetQuKaoHe.cell(lineIndex, colJianMian).value
    valueErJiFenLei = sheetQuKaoHe.cell(lineIndex, colErJiFenLei).value
    if valueChengBanCur is not None and valueLeiXing is not None \
        and valueLianxi is not None and valueJieJue is not None and valueManYi is not None \
            and valueErJiFenLei is not None and valueJianMian is not None:
        valueChengBanCur = valueChengBanCur.strip()
        valueLeiXing = valueLeiXing.strip()
        valueLianxi = valueLianxi.strip()
        valueJieJue = valueJieJue.strip()
        valueManYi = valueManYi.strip()
        valueJianMian = valueJianMian.strip()
        valueErJiFenLei = valueErJiFenLei.strip()
        isChuLiErJi = False
        logger.debug('{} - {}'.format(valueChengBanCur, valueLeiXing))
        if valueChengBanCur in listKeShi or valueChengBanCur in listSheQu:
            if valueLeiXing == '市派' or valueLeiXing == '直派':
                listKaoHeZhi[valueChengBanCur] = listKaoHeZhi[valueChengBanCur] + 1
                if valueLianxi == '已联系' or valueLianxi == '联系':
                    listLianXiZhi[valueChengBanCur] = listLianXiZhi[valueChengBanCur] + 1
                if valueLianxi == '未联系':
                    listWeiLianXiZhi[valueChengBanCur] = listWeiLianXiZhi[valueChengBanCur] + 1
                    if isChuLiErJi is False:
                        isChuLiErJi = True
                        if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = listShiFenFenLei[valueChengBanCur][
                                                                                      valueErJiFenLei] + 1
                        else:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                if valueJieJue == '已解决' or valueJieJue == '解决':
                    listJieJueZhi[valueChengBanCur] = listJieJueZhi[valueChengBanCur] + 1
                if valueJieJue == '未解决':
                    listWeiJieJueZhi[valueChengBanCur] = listWeiJieJueZhi[valueChengBanCur] + 1
                    if isChuLiErJi is False:
                        isChuLiErJi = True
                        if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = listShiFenFenLei[valueChengBanCur][
                                                                                      valueErJiFenLei] + 1
                        else:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                if valueManYi == '满意' or valueManYi == '非常满意':
                    listManYiZhi[valueChengBanCur] = listManYiZhi[valueChengBanCur] + 1
                if valueManYi == '基本满意':
                    listJiBenZhi[valueChengBanCur] = listJiBenZhi[valueChengBanCur] + 1
                    if isChuLiErJi is False:
                        isChuLiErJi = True
                        if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = listShiFenFenLei[valueChengBanCur][
                                                                                      valueErJiFenLei] + 1
                        else:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                if valueManYi == '不满意' or valueManYi == '非常不满意':
                    listBuManYiZhi[valueChengBanCur] = listBuManYiZhi[valueChengBanCur] + 1
                    if isChuLiErJi is False:
                        if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = listShiFenFenLei[valueChengBanCur][
                                                                                      valueErJiFenLei] + 1
                        else:
                            listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                if valueJianMian == '见面' or valueJianMian == '已见面':
                    listJianMianZhi[valueChengBanCur] = listJianMianZhi[valueChengBanCur] + 1
                if valueJianMian == '未见面':
                    listWeiJianMianZhi[valueChengBanCur] = listWeiJianMianZhi[valueChengBanCur] + 1
                # sheetKaoHe.delete_rows(lineIndex)
                # maxRowQuanBu = maxRowQuanBu - 1
                sheetQuKaoHe.cell(lineIndex, colChuLi).value = '已处理-直派'
            else:
                if valueLeiXing == '区派' or valueLeiXing == '转派':
                    listKaoHeZhuan[valueChengBanCur] = listKaoHeZhuan[valueChengBanCur] + 1
                    if valueLianxi == '已联系' or valueLianxi == '联系':
                        listLianXiZhuan[valueChengBanCur] = listLianXiZhuan[valueChengBanCur] + 1
                    if valueLianxi == '未联系':
                        listWeiLianXiZhuan[valueChengBanCur] = listWeiLianXiZhuan[valueChengBanCur] + 1
                        if isChuLiErJi is False:
                            isChuLiErJi = True
                            if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = \
                                listShiFenFenLei[valueChengBanCur][
                                    valueErJiFenLei] + 1
                            else:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                    if valueJieJue == '已解决' or valueJieJue == '解决':
                        listJieJueZhuan[valueChengBanCur] = listJieJueZhuan[valueChengBanCur] + 1
                    if valueJieJue == '未解决':
                        listWeiJieJueZhuan[valueChengBanCur] = listWeiJieJueZhuan[valueChengBanCur] + 1
                        if isChuLiErJi is False:
                            isChuLiErJi = True
                            if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = \
                                listShiFenFenLei[valueChengBanCur][
                                    valueErJiFenLei] + 1
                            else:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                    if valueManYi == '满意' or valueManYi == '非常满意':
                        listManYiZhuan[valueChengBanCur] = listManYiZhuan[valueChengBanCur] + 1
                    if valueManYi == '基本满意':
                        listJiBenZhuan[valueChengBanCur] = listJiBenZhuan[valueChengBanCur] + 1
                        if isChuLiErJi is False:
                            isChuLiErJi = True
                            if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = \
                                listShiFenFenLei[valueChengBanCur][
                                    valueErJiFenLei] + 1
                            else:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                    if valueManYi == '不满意' or valueManYi == '非常不满意':
                        listBuManYiZhuan[valueChengBanCur] = listBuManYiZhuan[valueChengBanCur] + 1
                        if isChuLiErJi is False:
                            isChuLiErJi = True
                            if valueErJiFenLei in listShiFenFenLei[valueChengBanCur]:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = \
                                listShiFenFenLei[valueChengBanCur][
                                    valueErJiFenLei] + 1
                            else:
                                listShiFenFenLei[valueChengBanCur][valueErJiFenLei] = 1
                    if valueJianMian == '见面' or valueJianMian == '已见面':
                        listJianMianZhuan[valueChengBanCur] = listJianMianZhuan[valueChengBanCur] + 1
                    if valueJianMian == '未见面':
                        listWeiJianMianZhuan[valueChengBanCur] = listWeiJianMianZhuan[valueChengBanCur] + 1
                    sheetQuKaoHe.cell(lineIndex, colChuLi).value = '已处理-转派'
                else:
                    sheetQuKaoHe.cell(lineIndex, colChuLi).value = '未处理'
        else:
            sheetQuKaoHe.cell(lineIndex, colChuLi).value = ' 未处理 - 无承办部门'
            numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1
    else:
        sheetQuKaoHe.cell(lineIndex, colChuLi).value = ' 未处理 - 数据错误'
        numWeiChuLiKaoHe = numWeiChuLiKaoHe + 1

logger.debug('考核-转派：{}'.format(listKaoHeZhuan))
logger.debug('考核-直派：{}'.format(listKaoHeZhi))
logger.debug('联系-转派：{}'.format(listLianXiZhuan))
logger.debug('联系-直派：{}'.format(listLianXiZhi))
logger.debug('未联系-转派：{}'.format(listWeiLianXiZhuan))
logger.debug('未联系-直派：{}'.format(listWeiLianXiZhi))
logger.debug('解决-转派：{}'.format(listJieJueZhuan))
logger.debug('解决-直派：{}'.format(listJieJueZhi))
logger.debug('未解决-转派：{}'.format(listWeiJieJueZhuan))
logger.debug('未解决-直派：{}'.format(listWeiJieJueZhi))
logger.debug('满意-转派：{}'.format(listManYiZhuan))
logger.debug('满意-直派：{}'.format(listManYiZhi))
logger.debug('基本满意-转派：{}'.format(listJiBenZhuan))
logger.debug('基本满意-直派：{}'.format(listJiBenZhi))
logger.debug('不满意-转派：{}'.format(listBuManYiZhuan))
logger.debug('不满意-直派：{}'.format(listBuManYiZhi))
logger.debug('见面-直派：{}'.format(listJianMianZhi))
logger.debug('见面-转派：{}'.format(listJianMianZhuan))
logger.debug('未见面-直派：{}'.format(listWeiJianMianZhi))
logger.debug('未见面-转派：{}'.format(listWeiJianMianZhuan))
logger.debug('统计考核数据完成 用时{}秒'.format(round(time.time() - timeStart, 2)))

countJieDanZhi = 0
countJieDanZhuan = 0
countKaoHeZhi = 0
countKaoHeZhuan = 0
countShiKaoHeZhi = 0
countShiKaoHeZhuan = 0
countLianXiZhi = 0
countLianXiZhuan = 0
countWeiLianXiZhi = 0
countWeiLianXiZhuan = 0
countJieJueZhi = 0
countJieJueZhuan = 0
countWeiJieJueZhi = 0
countWeiJieJueZhuan = 0
countManYiZhi = 0
countManYiZhuan = 0
countJianMianZhi = 0
countJianMianZhuan = 0
countWeiJianMianZhi = 0
countWeiJianMianZhuan = 0
countJiBenZhi = 0
countJIBenZhuan = 0
countBuManYiZhi = 0
countBuManYiZhuan = 0
countKaoHeZhiKeShi = 0
countKaoHeZhuanKeShi = 0
countKaoHeZhiSheQu = 0
countKaoHeZhuanSheQu = 0

for indexTongJi in range(0, len(listKeShi)):
    nameTongJi = listKeShi[indexTongJi]
    countJieDanZhi = countJieDanZhi + listJieDanZhi[nameTongJi]
    countJieDanZhuan = countJieDanZhuan + listJieDanZhuan[nameTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhiKeShi = countKaoHeZhiKeShi + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countKaoHeZhuanKeShi = countKaoHeZhuanKeShi + listKaoHeZhuan[nameTongJi]
    countShiKaoHeZhi = countShiKaoHeZhi + listShiKaoHeZhi[nameTongJi]
    countShiKaoHeZhuan = countShiKaoHeZhuan + listShiKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]
    countJianMianZhi = countJianMianZhi + listJianMianZhi[nameTongJi]
    countJianMianZhuan = countJianMianZhuan + listJianMianZhuan[nameTongJi]
    countWeiJianMianZhi = countWeiJianMianZhi + listWeiJianMianZhi[nameTongJi]
    countWeiJianMianZhuan = countWeiJianMianZhuan + listWeiJianMianZhuan[nameTongJi]


for indexTongJi in range(0, len(listSheQu)):
    nameTongJi = listSheQu[indexTongJi]
    countJieDanZhi = countJieDanZhi + listJieDanZhi[nameTongJi]
    countJieDanZhuan = countJieDanZhuan + listJieDanZhuan[nameTongJi]
    countKaoHeZhi = countKaoHeZhi + listKaoHeZhi[nameTongJi]
    countKaoHeZhiSheQu = countKaoHeZhiSheQu + listKaoHeZhi[nameTongJi]
    countKaoHeZhuan = countKaoHeZhuan + listKaoHeZhuan[nameTongJi]
    countKaoHeZhuanSheQu = countKaoHeZhuanSheQu + listKaoHeZhuan[nameTongJi]
    countShiKaoHeZhi = countShiKaoHeZhi + listShiKaoHeZhi[nameTongJi]
    countShiKaoHeZhuan = countShiKaoHeZhuan + listShiKaoHeZhuan[nameTongJi]
    countLianXiZhi = countLianXiZhi + listLianXiZhi[nameTongJi]
    countLianXiZhuan = countLianXiZhuan + listLianXiZhuan[nameTongJi]
    countWeiLianXiZhi = countWeiLianXiZhi + listWeiLianXiZhi[nameTongJi]
    countWeiLianXiZhuan = countWeiLianXiZhuan + listWeiLianXiZhuan[nameTongJi]
    countJieJueZhi = countJieJueZhi + listJieJueZhi[nameTongJi]
    countJieJueZhuan = countJieJueZhuan + listJieJueZhuan[nameTongJi]
    countWeiJieJueZhi = countWeiJieJueZhi + listWeiJieJueZhi[nameTongJi]
    countWeiJieJueZhuan = countWeiJieJueZhuan + listWeiJieJueZhuan[nameTongJi]
    countManYiZhi = countManYiZhi + listManYiZhi[nameTongJi]
    countManYiZhuan = countManYiZhuan + listManYiZhuan[nameTongJi]
    countJiBenZhi = countJiBenZhi + listJiBenZhi[nameTongJi]
    countJIBenZhuan = countJIBenZhuan + listJiBenZhuan[nameTongJi]
    countBuManYiZhi = countBuManYiZhi + listBuManYiZhi[nameTongJi]
    countBuManYiZhuan = countBuManYiZhuan + listBuManYiZhuan[nameTongJi]
    countJianMianZhi = countJianMianZhi + listJianMianZhi[nameTongJi]
    countJianMianZhuan = countJianMianZhuan + listJianMianZhuan[nameTongJi]
    countWeiJianMianZhi = countWeiJianMianZhi + listWeiJianMianZhi[nameTongJi]
    countWeiJianMianZhuan = countWeiJianMianZhuan + listWeiJianMianZhuan[nameTongJi]

logger.debug('考核-转派：{}'.format(countKaoHeZhuan))
logger.debug('联系-转派：{}'.format(countLianXiZhuan))
logger.debug('未联系-转派：{}'.format(countWeiLianXiZhuan))
logger.debug('解决-转派：{}'.format(countJieJueZhuan))
logger.debug('未解决-转派：{}'.format(countWeiJieJueZhuan))
logger.debug('满意-转派：{}'.format(countManYiZhuan))
logger.debug('基本满意-转派：{}'.format(countJIBenZhuan))
logger.debug('不满意-转派：{}'.format(countBuManYiZhuan))
logger.debug('见面-转派：{}'.format(countJianMianZhuan))
logger.debug('未见面-转派：{}'.format(countWeiJianMianZhuan))

logger.debug('考核-直派：{}'.format(countKaoHeZhi))
logger.debug('联系-直派：{}'.format(countLianXiZhi))
logger.debug('未联系-直派：{}'.format(countWeiLianXiZhi))
logger.debug('解决-直派：{}'.format(countJieJueZhi))
logger.debug('未解决-直派：{}'.format(countWeiJieJueZhi))
logger.debug('满意-直派：{}'.format(countManYiZhi))
logger.debug('基本满意-直派：{}'.format(countJiBenZhi))
logger.debug('不满意-直派：{}'.format(countBuManYiZhi))
logger.debug('见面-直派：{}'.format(countJianMianZhi))
logger.debug('未见面-直派：{}'.format(countWeiJianMianZhi))

lvLianxiCount = 0
lvLianxiCountZhuan = 0
lvLianxiCountZhi = 0

lvJieJueCount = 0
lvJieJueCountZhuan = 0
lvJieJueCountZhi = 0

lvJianMianCount = 0
lvJianMianCountZhuan = 0
lvJianMianCountZhi = 0

lvManYiCount = 0
lvManYiCountZhuan = 0
lvManYiCountZhi = 0

lvJiBenCount = 0
lvJiBenCountZhuan = 0
lvJiBenCountZhi = 0

lvManYiFinallCountZhi = 0
lvManYiFinallCountZhuan = 0

luoFenZhuan = 0
if countKaoHeZhuan > 0:
    lvLianxiCountZhuan = round(countLianXiZhuan/countKaoHeZhuan*100, numDecimals)
    lvJieJueCountZhuan = round(countJieJueZhuan/countKaoHeZhuan*100, numDecimals)
    lvManYiCountZhuan = round(countManYiZhuan/countKaoHeZhuan*100, numDecimals)
    lvJianMianCountZhuan = round(countJianMianZhuan/countKaoHeZhuan * 100, numDecimals)
    lvJiBenCountZhuan = round(countJIBenZhuan/countKaoHeZhuan*100, numDecimals)
    lvManYiFinallCountZhuan = round(lvManYiCountZhuan+lvJiBenCountZhuan*0.8, numDecimals)
    luoFenZhuan = round(lvLianxiCountZhuan*0.1 + lvJieJueCountZhuan*0.5 + lvManYiFinallCountZhuan*0.4, numDecimals)
logger.debug('区派三率 考核{}件   响应率：{}  解决率：{}  满意率：{} 见面率：{}'.format(countKaoHeZhuan, lvLianxiCountZhuan
                                                                                    , lvJieJueCountZhuan
                                                                                    , lvManYiFinallCountZhuan
                                                                                    , lvJianMianCountZhuan))
luoFenZhi = 0
if countKaoHeZhi > 0:
    lvLianxiCountZhi = round(countLianXiZhi/countKaoHeZhi*100, numDecimals)
    lvJieJueCountZhi = round(countJieJueZhi/countKaoHeZhi*100, numDecimals)
    lvManYiCountZhi = round(countManYiZhi/countKaoHeZhi*100, numDecimals)
    lvJianMianCountZhi = round(countJianMianZhi/countKaoHeZhi * 100, numDecimals)
    lvJiBenCountZhi = round(countJiBenZhi/countKaoHeZhi*100, numDecimals)
    lvManYiFinallCountZhi = round(lvManYiCountZhi+lvJiBenCountZhi*0.8, numDecimals)
    luoFenZhi = round(lvLianxiCountZhi * 0.1 + lvJieJueCountZhi * 0.5 + lvManYiFinallCountZhi * 0.4, numDecimals)
logger.debug('市派三率 考核{}件  响应率：{}  解决率：{}  满意率：{} 见面率：{}'.format(countKaoHeZhi, lvLianxiCountZhi
                                                                                   , lvJieJueCountZhi
                                                                                   , lvManYiFinallCountZhi
                                                                                   , lvJianMianCountZhi))

if countKaoHeZhuan > 0 and countKaoHeZhi > 0:
    lvLianxiCount = round((lvLianxiCountZhuan + lvLianxiCountZhi)/2, numDecimals)
    lvJieJueCount = round((lvJieJueCountZhuan + lvJieJueCountZhi)/2, numDecimals)
    lvManYiCount = round((lvManYiCountZhuan + lvManYiCountZhi) / 2, numDecimals)
    lvJiBenCount = round((lvJiBenCountZhuan + lvJiBenCountZhi) / 2, numDecimals)
    lvJianMianCount = round((lvJianMianCountZhuan + lvJianMianCountZhi) / 2, numDecimals)
elif countKaoHeZhuan > 0:
    lvLianxiCount = round(lvLianxiCountZhuan, numDecimals)
    lvJieJueCount = round(lvJieJueCountZhuan, numDecimals)
    lvManYiCount = round(lvManYiCountZhuan, numDecimals)
    lvJiBenCount = round(lvJiBenCountZhuan, numDecimals)
    lvJianMianCount = round(lvJianMianCountZhuan, numDecimals)
else:
    lvLianxiCount = round(lvLianxiCountZhi, numDecimals)
    lvJieJueCount = round(lvJieJueCountZhi, numDecimals)
    lvManYiCount = round(lvManYiCountZhi, numDecimals)
    lvJiBenCount = round(lvJiBenCountZhi, numDecimals)
    lvJianMianCount = round(lvJianMianCountZhi, numDecimals)

lvManYiFinalCount = round(lvManYiCount + lvJiBenCount*0.8, numDecimals)
logger.debug('区级有效考核{}件 响应率：{}  解决率：{}  满意率：{} 见面率：{}'.format(countKaoHeZhuan + countKaoHeZhi
                                                                                 , lvLianxiCount, lvJieJueCount
                                                                                 , lvManYiFinalCount, lvJianMianCount))
luoFen = round(lvLianxiCount*0.1 + lvJieJueCount*0.5 + lvManYiFinalCount*0.4, numDecimals)
logger.debug('裸分：{}  裸分(四舍五入)：{}'.format(luoFen, round(luoFen)))


def make_pai_ming_shu_ju(list_src):
    for rowIndex in range(0, len(list_src)):
        buMenName = list_src[rowIndex]
        if listKaoHeZhi[buMenName] != 0:
            lvLianXiZhi[buMenName] = round(listLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100, numDecimals)
            lvWeiLianXiZhi[buMenName] = round(listWeiLianXiZhi[buMenName]/listKaoHeZhi[buMenName]*100, numDecimals)
            lvJieJueZhi[buMenName] = round(listJieJueZhi[buMenName] / listKaoHeZhi[buMenName]*100, numDecimals)
            lvWeiJieJueZhi[buMenName] = round(listWeiJieJueZhi[buMenName] / listKaoHeZhi[buMenName] * 100, numDecimals)
            lvManYiZhi[buMenName] = round(listManYiZhi[buMenName] / listKaoHeZhi[buMenName]*100, numDecimals)
            lvJiBenZhi[buMenName] = round(listJiBenZhi[buMenName] / listKaoHeZhi[buMenName]*100, numDecimals)
            lvBuManYiZhi[buMenName] = round(listBuManYiZhi[buMenName] / listKaoHeZhi[buMenName] * 100, numDecimals)
            lvManYiFinalZhi[buMenName] = round(lvManYiZhi[buMenName] + lvJiBenZhi[buMenName]*0.8, numDecimals)
            lvJianMianZhi[buMenName] = round(listJianMianZhi[buMenName] / listKaoHeZhi[buMenName] * 100, numDecimals)
            lvWeiJianMianZhi[buMenName] = round(listWeiJianMianZhi[buMenName] / listKaoHeZhi[buMenName] * 100, numDecimals)
        if listKaoHeZhuan[buMenName] != 0:
            lvLianXiZhuan[buMenName] = round(listLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100, numDecimals)
            lvWeiLianXiZhuan[buMenName] = round(listWeiLianXiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100, numDecimals)
            lvJieJueZhuan[buMenName] = round(listJieJueZhuan[buMenName]/listKaoHeZhuan[buMenName]*100, numDecimals)
            lvWeiJieJueZhuan[buMenName] = round(listWeiJieJueZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100, numDecimals)
            lvManYiZhuan[buMenName] = round(listManYiZhuan[buMenName]/listKaoHeZhuan[buMenName]*100, numDecimals)
            lvJiBenZhuan[buMenName] = round(listJiBenZhuan[buMenName]/listKaoHeZhuan[buMenName]*100)
            lvBuManYiZhuan[buMenName] = round(listBuManYiZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100, numDecimals)
            lvManYiFinalZhuan[buMenName] = round(lvManYiZhuan[buMenName] + lvJiBenZhuan[buMenName] * 0.8, numDecimals)
            lvJianMianZhuan[buMenName] = round(listJianMianZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100, numDecimals)
            lvWeiJianMianZhuan[buMenName] = round(listWeiJianMianZhuan[buMenName] / listKaoHeZhuan[buMenName] * 100, numDecimals)
        # 联系率计算平均值
        deFenLianXi = 0
        shiFenLianXi = 0
        deFenJieJue = 0
        shiFenJieJue = 0
        deFenShangMan = 0
        deFenJiBen = 0
        ShiFenBuManYi = 0
        if countKaoHeZhuan > 0 and countKaoHeZhi > 0:
            deFenLianXi = round((listLianXiZhi[buMenName]/countKaoHeZhi*100
                                 + listLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, numDecimals)
            shiFenLianXi = round((listWeiLianXiZhi[buMenName]/countKaoHeZhi*100
                                 + listWeiLianXiZhuan[buMenName]/countKaoHeZhuan*100)/2, numDecimals)
            deFenJieJue = round((listJieJueZhi[buMenName]/countKaoHeZhi*100
                                 + listJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, numDecimals)
            shiFenJieJue = round((listWeiJieJueZhi[buMenName]/countKaoHeZhi*100
                                 + listWeiJieJueZhuan[buMenName]/countKaoHeZhuan*100)/2, numDecimals)
            deFenShangMan = round((listManYiZhi[buMenName]/countKaoHeZhi*100
                                   + listManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, numDecimals)
            deFenJiBen = round((listJiBenZhi[buMenName]/countKaoHeZhi*100
                                + listJiBenZhuan[buMenName]/countKaoHeZhuan*100)/2, numDecimals)
            ShiFenBuManYi = round((listBuManYiZhi[buMenName]/countKaoHeZhi*100
                                   + listBuManYiZhuan[buMenName]/countKaoHeZhuan*100)/2, numDecimals)
        elif countKaoHeZhuan > 0:
            deFenLianXi = round(listLianXiZhuan[buMenName]/countKaoHeZhuan*100, numDecimals)
            shiFenLianXi = round(listWeiLianXiZhuan[buMenName]/countKaoHeZhuan*100, numDecimals)
            deFenJieJue = round(listJieJueZhuan[buMenName]/countKaoHeZhuan*100, numDecimals)
            shiFenJieJue = round(listWeiJieJueZhuan[buMenName]/countKaoHeZhuan*100, numDecimals)
            deFenShangMan = round(listManYiZhuan[buMenName]/countKaoHeZhuan*100, numDecimals)
            deFenJiBen = round(listJiBenZhuan[buMenName]/countKaoHeZhuan*100, numDecimals)
            ShiFenBuManYi = round(listBuManYiZhuan[buMenName]/countKaoHeZhuan*100, numDecimals)
        elif countKaoHeZhi > 0:
            deFenLianXi = round(listLianXiZhi[buMenName]/countKaoHeZhi*100, numDecimals)
            shiFenLianXi = round(listWeiLianXiZhi[buMenName]/countKaoHeZhi*100, numDecimals)
            deFenJieJue = round(listJieJueZhi[buMenName]/countKaoHeZhi*100, numDecimals)
            shiFenJieJue = round(listWeiJieJueZhi[buMenName]/countKaoHeZhi*100, numDecimals)
            deFenShangMan = round(listManYiZhi[buMenName]/countKaoHeZhi*100, numDecimals)
            deFenJiBen = round(listJiBenZhi[buMenName]/countKaoHeZhi*100, numDecimals)
            ShiFenBuManYi = round(listBuManYiZhi[buMenName]/countKaoHeZhi*100, numDecimals)

        deFenManYi = round(deFenShangMan + deFenJiBen*0.8, numDecimals)
        shiFenManYi = round(ShiFenBuManYi + deFenJiBen*0.2, numDecimals)
        deFenLuoFen[buMenName] = round(deFenLianXi*0.1+deFenJieJue*0.5+deFenManYi*0.4, numDecimals)
        shiFenLuoFen[buMenName] = round(shiFenLianXi*0.1+shiFenJieJue*0.5+shiFenManYi*0.4, numDecimals)

        if lvLianXiZhi[buMenName] is not None and lvLianXiZhuan[buMenName] is not None:
            lvLianXiAverage[buMenName] = round((lvLianXiZhi[buMenName] + lvLianXiZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvLianXiZhi[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhi[buMenName]
            if lvLianXiZhuan[buMenName] is not None:
                lvLianXiAverage[buMenName] = lvLianXiZhuan[buMenName]

        # 未联系率计算平均值
        if lvWeiLianXiZhi[buMenName] is not None and lvWeiLianXiZhuan[buMenName] is not None:
            lvWeiLianXiAverage[buMenName] = round((lvWeiLianXiZhi[buMenName] + lvWeiLianXiZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvWeiLianXiZhi[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhi[buMenName]
            if lvWeiLianXiZhuan[buMenName] is not None:
                lvWeiLianXiAverage[buMenName] = lvWeiLianXiZhuan[buMenName]

        # 解决率计算平均值
        if lvJieJueZhi[buMenName] is not None and lvJieJueZhuan[buMenName] is not None:
            lvJieJueAverage[buMenName] = round((lvJieJueZhi[buMenName] + lvJieJueZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvJieJueZhi[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhi[buMenName]
            if lvJieJueZhuan[buMenName] is not None:
                lvJieJueAverage[buMenName] = lvJieJueZhuan[buMenName]

        # 未解决率计算平均值
        if lvWeiJieJueZhi[buMenName] is not None and lvWeiJieJueZhuan[buMenName] is not None:
            logger.debug('{} {} {}'.format(buMenName, lvWeiJieJueZhi[buMenName], lvWeiJieJueZhuan[buMenName]))
            lvWeiJieJueAverage[buMenName] = round((lvWeiJieJueZhi[buMenName] + lvWeiJieJueZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvWeiJieJueZhi[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhi[buMenName]
            if lvWeiJieJueZhuan[buMenName] is not None:
                lvWeiJieJueAverage[buMenName] = lvWeiJieJueZhuan[buMenName]

        # 满意率计算平均值
        if lvManYiZhi[buMenName] is not None and lvManYiZhuan[buMenName] is not None:
            lvManYiAverage[buMenName] = round((lvManYiZhi[buMenName] + lvManYiZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvManYiZhi[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhi[buMenName]
            if lvManYiZhuan[buMenName] is not None:
                lvManYiAverage[buMenName] = lvManYiZhuan[buMenName]

        # 基本满意率计算平均值
        if lvJiBenZhi[buMenName] is not None and lvJiBenZhuan[buMenName] is not None:
            lvJiBenAverage[buMenName] = round((lvJiBenZhi[buMenName] + lvJiBenZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvJiBenZhi[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhi[buMenName]
            if lvJiBenZhuan[buMenName] is not None:
                lvJiBenAverage[buMenName] = lvJiBenZhuan[buMenName]

        # 不满意率计算平均值
        if lvBuManYiZhi[buMenName] is not None and lvBuManYiZhuan[buMenName] is not None:
            lvBuManYiAverage[buMenName] = round((lvBuManYiZhi[buMenName] + lvBuManYiZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvBuManYiZhi[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhi[buMenName]
            if lvBuManYiZhuan[buMenName] is not None:
                lvBuManYiAverage[buMenName] = lvBuManYiZhuan[buMenName]

        # 最终满意率计算平均值
        if lvManYiFinalZhi[buMenName] is not None and lvManYiFinalZhuan[buMenName] is not None:
            lvManYiFinalAverage[buMenName] = round((lvManYiFinalZhi[buMenName] + lvManYiFinalZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvManYiFinalZhi[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhi[buMenName]
            if lvManYiFinalZhuan[buMenName] is not None:
                lvManYiFinalAverage[buMenName] = lvManYiFinalZhuan[buMenName]
        # 见面率计算平均值
        if lvJianMianZhi[buMenName] is not None and lvJianMianZhuan[buMenName] is not None:
            lvJianMianAverage[buMenName] = round((lvJianMianZhi[buMenName] + lvJianMianZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvJianMianZhi[buMenName] is not None:
                lvJianMianAverage[buMenName] = lvJianMianZhi[buMenName]
            if lvJianMianZhuan[buMenName] is not None:
                lvJianMianAverage[buMenName] = lvJianMianZhuan[buMenName]
        # 未见面率计算平均值
        if lvWeiJianMianZhi[buMenName] is not None and lvWeiJianMianZhuan[buMenName] is not None:
            lvWeiJianMianAverage[buMenName] = round((lvWeiJianMianZhi[buMenName] + lvWeiJianMianZhuan[buMenName]) / 2, numDecimals)
        else:
            if lvWeiJianMianZhi[buMenName] is not None:
                lvWeiJianMianAverage[buMenName] = lvWeiJianMianZhi[buMenName]
            if lvWeiJianMianZhuan[buMenName] is not None:
                lvWeiJianMianAverage[buMenName] = lvWeiJianMianZhuan[buMenName]

        logger.debug('{} 直派 考核：{}  联系：{} 联系率：{} 解决：{} 解决率：{} 满意：{}  基本满意：{} 满意率：{} '
                     '见面：{}  未见面：{} 见面率：{}'
                     .format(buMenName, listKaoHeZhi[buMenName], listLianXiZhi[buMenName], lvLianXiZhi[buMenName]
                             , listJieJueZhi[buMenName], lvJieJueZhi[buMenName]
                             , listManYiZhi[buMenName], listJiBenZhi[buMenName], lvManYiFinalZhi[buMenName]
                             , listJianMianZhi[buMenName], listWeiJianMianZhi[buMenName], lvJianMianZhi[buMenName]))
        logger.debug('{} 转派 考核：{}  联系：{} 联系率：{} 解决：{} 解决率：{} 满意：{}  基本满意：{} 满意率：{}'
                     '见面：{}  未见面：{} 见面率：{}'
                     .format(buMenName, listKaoHeZhuan[buMenName], listLianXiZhuan[buMenName], lvLianXiZhuan[buMenName]
                             , listJieJueZhuan[buMenName], lvJieJueZhuan[buMenName]
                             , listManYiZhuan[buMenName], listJiBenZhuan[buMenName], lvManYiFinalZhuan[buMenName]
                             , listJianMianZhuan[buMenName], listWeiJianMianZhuan[buMenName], lvJianMianZhuan[buMenName]))
        logger.debug('{} 平均  联系率：{}  解决率：{} 满意率：{} 见面率：{}'
                     .format(buMenName, lvLianXiAverage[buMenName], lvJieJueAverage[buMenName],
                             lvManYiFinalAverage[buMenName], lvJianMianAverage[buMenName]))


make_pai_ming_shu_ju(listKeShi)
make_pai_ming_shu_ju(listSheQu)

sheetKeShiResult.cell(1, 1).value = strTitleKeShi
sheetSheQuResult.cell(1, 1).value = strTitleSheQu

strMain = strMain + '我街道接收工单{}件'.format(countJieDanZhi+countJieDanZhuan)
strMain = strMain + '（区派{}件，市派{}件），'.format(countJieDanZhuan, countJieDanZhi)
strMain = strMain + '纳入市级考核范围{}件'.format(countShiKaoHeZhi+countShiKaoHeZhuan)
strMain = strMain + '（区派{}件，市派{}件），'.format(countShiKaoHeZhuan, countShiKaoHeZhi)
strMain = strMain + '区级有效回访{}件'.format(countKaoHeZhi+countKaoHeZhuan)
strMain = strMain + '（区派{}件，市派{}件），'.format(countKaoHeZhuan, countKaoHeZhi)
strMain = strMain + '联系{}件，'.format(countLianXiZhi+countLianXiZhuan)
strMain = strMain + '未联系{}件，'.format(countWeiLianXiZhi+countWeiLianXiZhuan)
strMain = strMain + '联系率{}%；'.format(round(lvLianxiCount, numDecimals))
strMain = strMain + '解决{}件，'.format(countJieJueZhi+countJieJueZhuan)
strMain = strMain + '未解决{}件，'.format(countWeiJieJueZhi+countWeiJieJueZhuan)
strMain = strMain + '解决率{}%；'.format(round(lvJieJueCount, numDecimals))
strMain = strMain + '满意{}件，'.format(countManYiZhi+countManYiZhuan)
strMain = strMain + '基本满意{}件，'.format(countJiBenZhi+countJIBenZhuan)
strMain = strMain + '不满意{}件，'.format(countBuManYiZhi+countBuManYiZhuan)
strMain = strMain + '满意率{}%；'.format(round(lvManYiFinalCount, numDecimals))
strMain = strMain + '见面{}件，'.format(countJianMianZhi+countJianMianZhuan)
strMain = strMain + '未见面{}件，'.format(countWeiJianMianZhi+countWeiJianMianZhuan)
strMain = strMain + '见面率{}%；综合得分{}。'.format(round(lvJianMianCount, numDecimals), round(luoFen, numDecimals))


def insert_into_sheet(sheet_insert, insert_index, inset_bu_men, de_fen, is_she_qu=0):
    rowInsert = insert_index
    sheet_insert.cell(rowInsert, 2).value = inset_bu_men            # 承办部门
    logger.debug('{} 接单量：{}  转派：{}   '
                 '直派：{} '.format(inset_bu_men, listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]
                                   , listJieDanZhuan[inset_bu_men], listJieDanZhi[inset_bu_men]))
    if is_she_qu == 0:
        listJieDanKeShi[inset_bu_men] = listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]
        listShiFenKeshi[inset_bu_men] = shiFenLuoFen[inset_bu_men]
    else:
        listJieDanSheQu[inset_bu_men] = listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]
        listShiFenSheQu[inset_bu_men] = shiFenLuoFen[inset_bu_men]

    if listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colJieDanCount).value = listJieDanZhi[inset_bu_men] + listJieDanZhuan[inset_bu_men]  # 接单总量(件)-总量
    if listJieDanZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colJieDanCountZhuan).value = listJieDanZhuan[inset_bu_men]  # 接单总量(件)-转派
    if listJieDanZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colJieDanCountZhi).value = listJieDanZhi[inset_bu_men]  # 接单总量(件)-直派
    if listShiKaoHeZhi[inset_bu_men] + listShiKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colShiKaoHeCount).value = listShiKaoHeZhi[inset_bu_men] + listShiKaoHeZhuan[inset_bu_men]  # 纳入市级考核(件)-总量
    if listShiKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colShiKaoHeCountZhuan).value = listShiKaoHeZhuan[inset_bu_men]  # 纳入市级考核(件)-转派
    if listShiKaoHeZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colShiKaoHeCountZhi).value = listShiKaoHeZhi[inset_bu_men]  # 纳入市级考核(件)-直派
    if listKaoHeZhi[inset_bu_men] + listKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colQuKaoHeCount).value = listKaoHeZhi[inset_bu_men] + listKaoHeZhuan[inset_bu_men]  # 纳入考核(件)-总量
    if listKaoHeZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanKaoHe).value = listKaoHeZhuan[inset_bu_men]  # 转派-考核
    if listLianXiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLian).value = listLianXiZhuan[inset_bu_men]  # 转派-联系
    if listWeiLianXiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanLianNo).value = listWeiLianXiZhuan[inset_bu_men]  # 转派-未联系
    if listJieJueZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJie).value = listJieJueZhuan[inset_bu_men]  # 转派-解决
    if listWeiJieJueZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJieNo).value = listWeiJieJueZhuan[inset_bu_men]  # 转派-未解决
    if listManYiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanMan).value = listManYiZhuan[inset_bu_men]  # 转派-满意
    if listJiBenZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJiBen).value = listJiBenZhuan[inset_bu_men]  # 转派-基本满意
    if listBuManYiZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanManNo).value = listBuManYiZhuan[inset_bu_men]  # 转派-不满意
    if listJianMianZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJian).value = listJianMianZhuan[inset_bu_men]  # 转派-见面
    if listWeiJianMianZhuan[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhuanJianNo).value = listWeiJianMianZhuan[inset_bu_men]  # 转派-未见面
    if lvLianXiZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanlvLian).value = lvLianXiZhuan[inset_bu_men]  # 转派-响应率
    if lvJieJueZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanLvJie).value = lvJieJueZhuan[inset_bu_men]  # 转派-解决率
    if lvManYiFinalZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanLvMan).value = lvManYiFinalZhuan[inset_bu_men]  # 转派-满意率
    if lvJianMianZhuan[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhuanLvJian).value = lvJianMianZhuan[inset_bu_men]  # 转派-见面率
    if listKaoHeZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiKaoHe).value = listKaoHeZhi[inset_bu_men]  # 直派-考核
    if listLianXiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLian).value = listLianXiZhi[inset_bu_men]  # 直派-联系
    if listWeiLianXiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiLianNo).value = listWeiLianXiZhi[inset_bu_men]  # 直派-未联系
    if listJieJueZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJie).value = listJieJueZhi[inset_bu_men]  # 直派-解决
    if listWeiJieJueZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJieNo).value = listWeiJieJueZhi[inset_bu_men]  # 直派-未解决
    if listManYiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiMan).value = listManYiZhi[inset_bu_men]  # 直派-满意
    if listJiBenZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJiBen).value = listJiBenZhi[inset_bu_men]  # 直派-基本满意
    if listBuManYiZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiManNo).value = listBuManYiZhi[inset_bu_men]  # 直派-不满意
    if listJianMianZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJian).value = listJianMianZhi[inset_bu_men]  # 直派-见面
    if listWeiJianMianZhi[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colZhiJianNo).value = listWeiJianMianZhi[inset_bu_men]  # 直派-未见面
    if lvLianXiZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhilvLian).value = lvLianXiZhi[inset_bu_men]  # 直派-响应率
    if lvJieJueZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhiLvJie).value = lvJieJueZhi[inset_bu_men]  # 直派-解决率
    if lvManYiFinalZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhiLvMan).value = lvManYiFinalZhi[inset_bu_men]  # 直派-满意率
    if lvJianMianZhi[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colZhilvJian).value = lvJianMianZhi[inset_bu_men]  # 直派-见面率
    if lvLianXiAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgLian).value = lvLianXiAverage[inset_bu_men]  # 平均响应率
    if lvJieJueAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgJie).value = lvJieJueAverage[inset_bu_men]  # 平均解决率
    if lvManYiFinalAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgMan).value = lvManYiFinalAverage[inset_bu_men]  # 平均满意率
    if lvJianMianAverage[inset_bu_men] is not None:
        sheet_insert.cell(rowInsert, colAvgJian).value = lvJianMianAverage[inset_bu_men]  # 平均见面率
    sheet_insert.cell(rowInsert, colDeFen).value = de_fen  # 得分
    if deFenLuoFen[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colDeFenLuoFen).value = deFenLuoFen[inset_bu_men]  # 裸分得分
    if shiFenLuoFen[inset_bu_men] != 0:
        sheet_insert.cell(rowInsert, colShiFenLuoFen).value = shiFenLuoFen[inset_bu_men]  # 裸分失分
    if is_she_qu == 1:
        sheet_insert.cell(rowInsert, colSuoShuPianQu).value = suoShuPianQu[inset_bu_men]  # 所属片区


def make_pai_ming(sheet_make, list_make, is_she_qu=0):
    tinyDictDeFen = {}
    tinyDictDeFenNone = {}
    for nameIndex in list_make:
        # nameIndex = list_make[indexName]
        lvManYiAll = None
        if listKaoHeZhi[nameIndex] + listKaoHeZhuan[nameIndex] > 0:
            if lvManYiAverage[nameIndex] is not None and lvJiBenAverage[nameIndex] is not None:
                lvManYiAll = lvManYiAverage[nameIndex]+lvJiBenAverage[nameIndex]*0.8
            else:
                if lvManYiAverage[nameIndex] is not None:
                    lvManYiAll = lvManYiAverage[nameIndex]
                if lvJiBenAverage[nameIndex] is not None:
                    lvManYiAll = lvJiBenAverage[nameIndex]*0.8
            numDeFen = round(lvLianXiAverage[nameIndex] * 0.1 + lvJieJueAverage[nameIndex] * 0.5 + lvManYiAll * 0.4)
            tinyDictDeFen[nameIndex] = numDeFen
        else:
            tinyDictDeFenNone[nameIndex] = listJieDanZhi[nameIndex] + listJieDanZhuan[nameIndex]
        # logger.debug('{} 得分：{} '.format(nameIndex, numDeFen))
    tinyDictDeFen = sorted(tinyDictDeFen.items(), key=lambda kv: kv[1], reverse=True)
    inertRow = 4
    for key in tinyDictDeFen:
        logger.debug('tinyDictDeFen {}行  承办部门：{} 得分：{}'.format(inertRow, key[0], key[1]))
        insert_into_sheet(sheet_make, inertRow, key[0], key[1], is_she_qu)
        inertRow = inertRow + 1
    tinyDictDeFenNone = sorted(tinyDictDeFenNone.items(), key=lambda kv: kv[1], reverse=True)
    for key in tinyDictDeFenNone:
        logger.debug('tinyDictDeFenNone {}行  承办部门：{}'.format(inertRow, key[0]))
        insert_into_sheet(sheet_make, inertRow, key[0], None, is_she_qu)
        inertRow = inertRow + 1

make_pai_ming(sheetKeShiResult, listKeShi)
make_pai_ming(sheetSheQuResult, listSheQu, 1)

strMsgCount = strMain
strMainKeshi = '备注：\r\n1.未接单的机关科室未在上表内列出\r\n2.' + strMain
# inset_he_ji_bei_zhu(sheetKeShiResult, strMainKeshi)
sheetKeShiResult.cell(sheetKeShiResult.max_row, 1).value = strMainKeshi
strMainSheQu = '备注：\r\n1.未接单的社区（村）未在上表内列出\r\n2.' + strMain
# inset_he_ji_bei_zhu(sheetSheQuResult, strMainSheQu)
sheetSheQuResult.cell(sheetSheQuResult.max_row, 1).value = strMainSheQu
# def inset_into_sheet_in_line(line)

bookQuKaoHe.save(fileResultKaoHe)
bookResultMuBan.save(fileResultSave)
# logger.debug('用时{}秒'.format(round(time.time() - timeStart, 2)))

logger.debug('listJieDanSheQu:{}'.format(listJieDanSheQu))
sortlistJieDanSheQu = dict(sorted(listJieDanSheQu.items(), key=operator.itemgetter(1), reverse=True))
logger.debug('sortlistJieDanSheQu:{}'.format(sortlistJieDanSheQu))

bookDaPing = openpyxl.load_workbook(fileDaPing)
sheetDaPingJieDanSheQu = bookDaPing['社区村接单量排行']
logger.debug('[内部] 拱辰街道接诉即办大屏排行榜.xlsx 社区村接单量排行')
DaPingJieDanSheQuKeys = list(sortlistJieDanSheQu.keys())
logger.debug('sortListJieDanKeShi.keys():{}'.format(DaPingJieDanSheQuKeys))
sheetDaPingJieDanSheQu.cell(1, 1).value = titleDaPingSheQuJieDan

# strceshi = "注：\r\n1.接单量统计时间为本考核月开始至今日9时派单时间；\r\n2." + strMsgCount
# sheetDaPingJieDanSheQu.cell(14, 1).value = "注：\r\n1.接单量统计时间为本考核月开始至今日9时派单时间；\r\n2." + strMsgCount
sheetDaPingJieDanSheQu.cell(sheetDaPingJieDanSheQu.max_row, 1).value = "注：\r\n" + strMsgCount
indexInsert = 0
for rowIndex in range(3, 13):
    bumenInsert = None
    while indexInsert < len(DaPingJieDanSheQuKeys):
        bumenInsert = DaPingJieDanSheQuKeys[indexInsert]
        if sortlistJieDanSheQu.get(bumenInsert) > 0:
            break
        indexInsert = indexInsert + 1

    if indexInsert < len(DaPingJieDanSheQuKeys):
        sheetDaPingJieDanSheQu.cell(rowIndex, 2).value = bumenInsert
        sheetDaPingJieDanSheQu.cell(rowIndex, 3).value = sortlistJieDanSheQu.get(bumenInsert)
        sheetDaPingJieDanSheQu.cell(rowIndex, 4).value = listWeiJieJueZhi[bumenInsert] + listWeiJieJueZhuan[bumenInsert]
        sheetDaPingJieDanSheQu.cell(rowIndex, 5).value = fuZeRen[bumenInsert]
        sheetDaPingJieDanSheQu.cell(rowIndex, 6).value = suoShuPianQu[bumenInsert]
        sheetDaPingJieDanSheQu.cell(rowIndex, 7).value = zhuGuanLingDao[bumenInsert]
        logger.debug('[{}]:[{}]:[{}]:[{}]:[{}]'.format(bumenInsert, sortlistJieDanSheQu.get(bumenInsert),
                                                       fuZeRen[bumenInsert], suoShuPianQu[bumenInsert],
                                                       zhuGuanLingDao[bumenInsert]))
    else:
        sheetDaPingJieDanSheQu.cell(rowIndex, 2).value = ""
        sheetDaPingJieDanSheQu.cell(rowIndex, 3).value = ""
        sheetDaPingJieDanSheQu.cell(rowIndex, 4).value = ""
        sheetDaPingJieDanSheQu.cell(rowIndex, 5).value = ""
        sheetDaPingJieDanSheQu.cell(rowIndex, 6).value = ""
        sheetDaPingJieDanSheQu.cell(rowIndex, 7).value = ""
    indexInsert = indexInsert + 1


logger.debug('listJieDanKeShi:{}'.format(listJieDanKeShi))
sortListJieDanKeShi = dict(sorted(listJieDanKeShi.items(), key=operator.itemgetter(1), reverse=True))
logger.debug('sortListJieDanKeShi:{}'.format(sortListJieDanKeShi))

sheetDaPingJieDanKeShi = bookDaPing['科室接单量排行']
logger.debug('[内部] 拱辰街道接诉即办大屏排行榜.xlsx 科室接单量排行')
DaPingJieDanKeShiKeys = list(sortListJieDanKeShi.keys())
logger.debug('sortListJieDanKeShi.keys():{}'.format(DaPingJieDanKeShiKeys))

sheetDaPingJieDanKeShi.cell(1, 1).value = titleDaPingKeShiJieDan
# sheetDaPingJieDanKeShi.cell(14, 1).value = "注：\r\n1.接单量统计时间为本考核月开始至今日9时派单时间；\r\n2." + strMsgCount
sheetDaPingJieDanKeShi.cell(sheetDaPingJieDanKeShi.max_row, 1).value = "注：\r\n" + strMsgCount
indexInsert = 0
for rowIndex in range(3, 13):
    bumenInsert = None
    while indexInsert < len(DaPingJieDanKeShiKeys):
        bumenInsert = DaPingJieDanKeShiKeys[indexInsert]
        if sortListJieDanKeShi.get(bumenInsert) > 0:
            break
        indexInsert = indexInsert + 1
    if bumenInsert is not None and indexInsert < len(DaPingJieDanSheQuKeys):
        sheetDaPingJieDanKeShi.cell(rowIndex, 2).value = bumenInsert
        sheetDaPingJieDanKeShi.cell(rowIndex, 3).value = sortListJieDanKeShi.get(bumenInsert)
        sheetDaPingJieDanKeShi.cell(rowIndex, 4).value = listWeiJieJueZhi[bumenInsert] + listWeiJieJueZhuan[bumenInsert]
        sheetDaPingJieDanKeShi.cell(rowIndex, 5).value = fuZeRen[bumenInsert]
        sheetDaPingJieDanKeShi.cell(rowIndex, 6).value = zhuGuanLingDao[bumenInsert]
        logger.debug('[{}]:[{}]:[{}]:[{}]'.format(bumenInsert, sortListJieDanKeShi.get(bumenInsert),
                                                  fuZeRen[bumenInsert], zhuGuanLingDao[bumenInsert]))
    else:
        sheetDaPingJieDanKeShi.cell(rowIndex, 2).value = ""
        sheetDaPingJieDanKeShi.cell(rowIndex, 3).value = ""
        sheetDaPingJieDanKeShi.cell(rowIndex, 4).value = ""
        sheetDaPingJieDanKeShi.cell(rowIndex, 5).value = ""
        sheetDaPingJieDanKeShi.cell(rowIndex, 6).value = ""
    indexInsert = indexInsert + 1


logger.debug('listShiFenSheQu:{}'.format(listShiFenSheQu))
sortlistShiFenSheQu = dict(sorted(listShiFenSheQu.items(), key=operator.itemgetter(1), reverse=True))
logger.debug('sortlistShiFenSheQu:{}'.format(sortlistShiFenSheQu))
sheetDaPingShiFenSheQu = bookDaPing['社区村裸分失分排行']
logger.debug('[内部] 拱辰街道接诉即办大屏排行榜.xlsx 社区村裸分失分排行')
DaPingShiFenSheQuKeys = list(sortlistShiFenSheQu.keys())
logger.debug('sortListJieDanKeShi.keys():{}'.format(DaPingShiFenSheQuKeys))
sheetDaPingShiFenSheQu.cell(sheetDaPingShiFenSheQu.max_row, 1).value = "注：\r\n" + strMsgCount

sheetDaPingShiFenSheQu.cell(1, 1).value = titleDaPingSheQuShiFen
for rowIndex in range(3, 13):
    bumenInsert = DaPingShiFenSheQuKeys[rowIndex-3]
    logger.debug('[{}]:[{}]'.format(bumenInsert, listShiFenFenLei[bumenInsert]))
    sortListShiFenFenLei = dict(sorted(listShiFenFenLei[bumenInsert].items(), key=operator.itemgetter(1), reverse=True))
    logger.debug('[{}]:[{}]'.format(bumenInsert, sortListShiFenFenLei))
    sortListShiFenFenLeiKeys = list(sortListShiFenFenLei.keys())
    logger.debug('sortListJieDanKeShi.keys():{}'.format(sortListShiFenFenLeiKeys))
    valueErJieShiFen = None
    if len(sortListShiFenFenLeiKeys) > 2:
        valueErJieShiFen = sortListShiFenFenLeiKeys[0] + '、' + sortListShiFenFenLeiKeys[1] + '、' + \
                           sortListShiFenFenLeiKeys[2]
    elif len(sortListShiFenFenLeiKeys) == 2:
        valueErJieShiFen = sortListShiFenFenLeiKeys[0] + '、' + sortListShiFenFenLeiKeys[1]
    elif len(sortListShiFenFenLeiKeys) == 1:
        valueErJieShiFen = sortListShiFenFenLeiKeys[0]
    sheetDaPingShiFenSheQu.cell(rowIndex, 2).value = bumenInsert
    sheetDaPingShiFenSheQu.cell(rowIndex, 3).value = listKaoHeZhi[bumenInsert] + listKaoHeZhuan[bumenInsert]
    sheetDaPingShiFenSheQu.cell(rowIndex, 4).value = listWeiJieJueZhi[bumenInsert] + listWeiJieJueZhuan[bumenInsert]
    sheetDaPingShiFenSheQu.cell(rowIndex, 5).value = deFenLuoFen[bumenInsert]
    sheetDaPingShiFenSheQu.cell(rowIndex, 6).value = sortlistShiFenSheQu.get(bumenInsert)
    sheetDaPingShiFenSheQu.cell(rowIndex, 7).value = valueErJieShiFen
    sheetDaPingShiFenSheQu.cell(rowIndex, 8).value = fuZeRen[bumenInsert]
    sheetDaPingShiFenSheQu.cell(rowIndex, 9).value = zhuGuanLingDao[bumenInsert]
    logger.debug('[{}]:[{}]:[{}]:[{}]:[]:[{}]:[{}]'.format(bumenInsert
                                                             , listKaoHeZhi[bumenInsert] + listKaoHeZhuan[bumenInsert]
                                                             , deFenLuoFen[bumenInsert], sortlistShiFenSheQu.get(bumenInsert)
                                                             , fuZeRen[bumenInsert], zhuGuanLingDao[bumenInsert]))
    logger.debug('[{}]:[{}]'.format(bumenInsert, listShiFenFenLei[bumenInsert]))

logger.debug('listShiFenKeshi:{}'.format(listShiFenKeshi))
sortlistShiFenKeshi = dict(sorted(listShiFenKeshi.items(), key=operator.itemgetter(1), reverse=True))
logger.debug('sortlistShiFenKeshi:{}'.format(sortlistShiFenKeshi))
sheetDaPingShiFenKeShi = bookDaPing['科室裸分失分排行']
logger.debug('[内部] 拱辰街道接诉即办大屏排行榜.xlsx 科室裸分失分排行')
DaPingShiFenKeShiKeys = list(sortlistShiFenKeshi.keys())
logger.debug('sortListJieDanKeShi.keys():{}'.format(DaPingShiFenKeShiKeys))
sheetDaPingShiFenKeShi.cell(sheetDaPingShiFenKeShi.max_row, 1).value = "注：\r\n" + strMsgCount

sheetDaPingShiFenKeShi.cell(1, 1).value = titleDaPingKeShiShiFen
for rowIndex in range(3, 13):
    bumenInsert = DaPingShiFenKeShiKeys[rowIndex-3]
    logger.debug('[{}]:[{}]'.format(bumenInsert, listShiFenFenLei[bumenInsert]))
    sortListShiFenFenLei = dict(sorted(listShiFenFenLei[bumenInsert].items(), key=operator.itemgetter(1), reverse=True))
    logger.debug('[{}]:[{}]'.format(bumenInsert, sortListShiFenFenLei))
    sortListShiFenFenLeiKeys = list(sortListShiFenFenLei.keys())
    logger.debug('sortListJieDanKeShi.keys():{}'.format(sortListShiFenFenLeiKeys))
    valueErJieShiFen = None
    if len(sortListShiFenFenLeiKeys) > 2:
        valueErJieShiFen = sortListShiFenFenLeiKeys[0] + '、' + sortListShiFenFenLeiKeys[1] + '、' + \
                           sortListShiFenFenLeiKeys[2]
    elif len(sortListShiFenFenLeiKeys) == 2:
        valueErJieShiFen = sortListShiFenFenLeiKeys[0] + '、' + sortListShiFenFenLeiKeys[1]
    elif len(sortListShiFenFenLeiKeys) == 1:
        valueErJieShiFen = sortListShiFenFenLeiKeys[0]
    sheetDaPingShiFenKeShi.cell(rowIndex, 2).value = bumenInsert
    sheetDaPingShiFenKeShi.cell(rowIndex, 3).value = listKaoHeZhi[bumenInsert] + listKaoHeZhuan[bumenInsert]
    sheetDaPingShiFenKeShi.cell(rowIndex, 4).value = listWeiJieJueZhi[bumenInsert] + listWeiJieJueZhuan[bumenInsert]
    sheetDaPingShiFenKeShi.cell(rowIndex, 5).value = deFenLuoFen[bumenInsert]
    sheetDaPingShiFenKeShi.cell(rowIndex, 6).value = sortlistShiFenKeshi.get(bumenInsert)
    sheetDaPingShiFenKeShi.cell(rowIndex, 7).value = valueErJieShiFen
    sheetDaPingShiFenKeShi.cell(rowIndex, 8).value = fuZeRen[bumenInsert]
    sheetDaPingShiFenKeShi.cell(rowIndex, 9).value = zhuGuanLingDao[bumenInsert]
    logger.debug('[{}]:[{}]:[{}]:[{}]:[]:[{}]:[{}]'.format(bumenInsert
                                                             , listKaoHeZhi[bumenInsert] + listKaoHeZhuan[bumenInsert]
                                                             , deFenLuoFen[bumenInsert], sortlistShiFenKeshi.get(bumenInsert)
                                                             , fuZeRen[bumenInsert], zhuGuanLingDao[bumenInsert]))

bookDaPing.save(fileDaPingSave)
logger.debug('用时{}秒'.format(round(time.time() - timeStart, 2)))

listLuoFenTongJiTitle = ['日期', '接单总量', '考核工单', '市派工单', '市派联系率', '市派解决率', '市派满意率',
                         '市派得分', '区派工单', '区派联系率', '区派解决率', '区派满意率', '区派得分', '联系率', '解决率',
                         '满意率', '综合得分', '裸分得分', '裸分失分', '时间戳']
listLuoFenTongJiTitleCol = {}
# 打开 区级考核裸分情况统计表 2023.xlsx
bookLuoFenTongJi = None
sheetLuoFenTongJi = None
curSheetName = ''
if timeCur.day >= 19:
    curSheetName = '{}月'.format(timeCur.month + 1)
else:
    curSheetName = '{}月'.format(timeCur.month)
if os.path.exists(fileLuoFenTongJi):
    bookLuoFenTongJi = openpyxl.load_workbook(fileLuoFenTongJi, data_only=True)
    sheet_names = bookLuoFenTongJi.sheetnames
    if curSheetName in sheet_names:
        sheetLuoFenTongJi = bookLuoFenTongJi[curSheetName]
    else:
        sheetLuoFenTongJi = bookLuoFenTongJi.create_sheet(title=curSheetName, index=1)
        for indexTitle in range(0, len(listLuoFenTongJiTitle)):
            sheetLuoFenTongJi.cell(1, indexTitle + 1).value = listLuoFenTongJiTitle[indexTitle]
else:
    bookLuoFenTongJi = openpyxl.Workbook()  # 创建一个excel文件
    sheetLuoFenTongJi = bookLuoFenTongJi.active  # 获得一个的工作表
    sheetLuoFenTongJi.title = curSheetName
    for indexTitle in range(0, len(listLuoFenTongJiTitle)):
        sheetLuoFenTongJi.cell(1, indexTitle + 1).value = listLuoFenTongJiTitle[indexTitle]

if sheetLuoFenTongJi is not None:
    maxRowLuoFenTongJi = sheetLuoFenTongJi.max_row
    strToday = '{}-{}-{}'.format(timeCur.year, timeCur.month, timeCur.day)
    strTodayTime = '{}-{}-{} {}:{}:{}'.format(timeCur.year, timeCur.month, timeCur.day, timeCur.hour, timeCur.minute
                                             , timeCur.second)
    rowInsert = maxRowLuoFenTongJi + 1
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('日期') + 1).value = strToday
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('接单总量') + 1).value = countJieDanZhi + countJieDanZhuan
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('考核工单') + 1).value = countKaoHeZhi + countKaoHeZhuan
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('市派工单') + 1).value = countKaoHeZhi
    logger.debug('市派联系率 在{}列'.format(listLuoFenTongJiTitle.index('市派联系率') + 1))
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('市派联系率') + 1).value = round(lvLianxiCountZhi, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('市派解决率') + 1).value = round(lvJieJueCountZhi, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('市派满意率') + 1).value = round(lvManYiFinallCountZhi, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('市派得分') + 1).value = luoFenZhi
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('区派工单') + 1).value = countKaoHeZhuan
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('区派联系率') + 1).value = round(lvLianxiCountZhuan, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('区派解决率') + 1).value = round(lvJieJueCountZhuan, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('区派满意率') + 1).value = round(lvManYiFinallCountZhuan, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('区派得分') + 1).value = luoFenZhuan
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('联系率') + 1).value = round(lvLianxiCount, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('解决率') + 1).value = round(lvJieJueCount, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('满意率') + 1).value = round(lvManYiFinalCount, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('综合得分') + 1).value = round((luoFenZhi + luoFenZhuan)/2, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('裸分得分') + 1).value = round(luoFen, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('裸分失分') + 1).value = 100 - round(luoFen, numDecimals)
    sheetLuoFenTongJi.cell(rowInsert, listLuoFenTongJiTitle.index('时间戳') + 1).value = strTodayTime
    logger.debug(
        '{} 接单：{}件 考核{}件  市派：{}件 区派：{}件 联系率：{}% 解决率：{}% 满意率：{}% 裸分得分：{} 裸分失分：{} 时间戳：{}'.format(
            strToday, countJieDanZhi + countJieDanZhuan, countKaoHeZhi + countKaoHeZhuan, countKaoHeZhi,
            countKaoHeZhuan, round(lvLianxiCount, numDecimals), round(lvJieJueCount, numDecimals), round(lvManYiFinalCount, numDecimals), round(luoFen, numDecimals),
            100 - round(luoFen, numDecimals), strTodayTime))

bookLuoFenTongJi.save(fileLuoFenTongJi)


